﻿CREATE PROCEDURE sp__sec_MenuGroups_UI
@MenuID int = NULL,
@NameSpace nvarchar(512) = NULL,
@Groups xml
AS

DECLARE @G TABLE(cn sysname)

INSERT INTO @G
select T.c.value('@cn','sysname')A FROM  @Groups.nodes('//t_Group')  T(c)


--RAISERROR('NS:%s',16,1,@NameSpace)

--DECLARE @NSID int SELECT @NSID = ID FROM dbo.t_NameSpace WHERE NameSpace = ISNULL(@NameSpace,dbo.UserNameSpace())

SELECT cn GroupName FROM @G ORDER BY cn
--SELECT     StateID AS MenuID, principal_id AS GroupName, StateAccessID AS Access
--FROM         t_State_Group_Access
--WHERE StateID=@MenuID-- AND NameSpaceID = @NSID


SELECT     SA_1.AccessID, CASE WHEN principal_id IS NULL THEN 0 ELSE 1 END AS Checked, SA_1.StateAccessName AS AccessName, 
                      SA_1.StateID AS MenuID, SA_1.GroupName
FROM         (SELECT     SA.ID AS AccessID, S.ID AS StateID, SA.Name AS StateAccessName, t_ADsGroup.cn AS GroupName
                       FROM          t_State AS S CROSS JOIN
                                              ts_StateAccess AS SA CROSS JOIN
                                              @G t_ADsGroup
                       WHERE      (S.ID = @MenuID)) AS SA_1 LEFT OUTER JOIN
                      t_State_Group_Access AS SGA
ON SA_1.AccessID = SGA.StateAccessID AND SA_1.StateID = SGA.StateID AND SA_1.GroupName = SGA.principal_id
--WHERE SA.StateID = @MenuID
--SELECT GroupName,AccessID,Checked,AccessName FROM dbo.vx_MenuGroupAccess_Detailed
--WHERE MenuID=@MenuID AND NameSpaceID = @NSID


